PostgreSQL 事务并发控制

#PostgreSQL #事务并发控制

数据块结构

事务ID

事务ID 号是什么

testdb=# SELECT txid_current();

事务快照（Snapshot）

Snapshot = '100:104'

100 以前事务全部结束。
100 以后是还未产生的事务。
没有还未结束的事务。

SELECT txid_curren_snapshot();
100:104:100,102

xmin

最早仍在活动的txid 。这个事务ID号之前的所有的以前的事务要么提交并可见，要么就回滚并停止。
理解： xmin 以前的所有事务都结束了。

xmax

尚未分配的txid 。截至此快照时，所有大于或者等于此值的txid 尚未启动，因此其他事务无法看见还没有产生的事务ID。

xip_list

截至此快照时的所有活动txid。该列表仅包含xmin和xmax 之间的活动的txid。已
例如： 100 到104 总共有 100，101，102，103，104 四个事务ID ，现目前只有100，和102 还在执行，101，1003 都结束了（commit，rollback）

事务号和可见性

Snapshot = '100:104':100,102

100 以前事务全部结束，100 以前全部可见。
104 还未产生的事务，104 以后的事务全部不可见。
100 和102 还未结束，事务修改的数据不可见。

查看事务号和事务快照

A事务

psqlgres> psql -U postgres -d testdb
begin;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =854;
UPDATE payment SET amount=1.1 WHERE payment_id=854;
SELECT txid_current_snapshot();

B事务

psqlgres> psql -U postgres -d testdb
begin;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT txid_current();
SELECT * FROM payment where payment_id =10244;
UPDATE payment SET amount=1.2 WHERE payment_id=10244;
SELECT txid_current_snapshot();

C事务

psqlgres> psql -U postgres -d testdb
begin;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =9643;
UPDATE payment SET amount=1.3 WHERE payment_id=9643;
SELECT txid_current_snapshot();

D事务

psqlgres> psql -U postgres -d testdb
begin;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =1862;
UPDATE payment SET amount=1.4 WHERE payment_id=1862;
SELECT txid_current_snapshot();

E事务

psqlgres> psql -U postgres -d testdb
begin;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =8965;
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

提交第三个和第四个事务

select * from payment where payment_id in (854,10244,9643,1862) ;

COMMIT log

commit log 保存事务的提交状态，和对应的事务ID号。

COMMITTED 为已提交
ABORTED 为未提交
IN_PROGRESS 正在执行。

commit log 文件

数据库正常关闭或者检查点发生时，clog数据写入pg_xact目录下的文件中
命名习惯：0000 0001 ……
数据库启动时从pg_xact文件中加载数据
由Vacuum 进程定期处理不需要的事务快照。

commit log 性能调优

#define HEAP_XMIN_COMMITTED0x0100/* t_xmincommitted */
#define HEAP_XMIN_INVALID0x0200/* t_xmininvalid/aborted */
#define HEAP_XMAX_COMMITTED0x0400/* t_xmaxcommitted */
#define HEAP_XMAX_INVALID0x0800/* t_xmaxinvalid/aborted */

事务的隔离级别

READ COMMITTED
REPEATABLE READ
SERIALIZABLE

事务隔离级别和事务ID号

事务的隔离级别和事务ID号

T1: 开启事务A，快照为Snapshot = '200:200'
T2: 开启事务B，快照为Snapshot = '200:200'
T3: 开启事务C，快照为Snapshot = '200:200'
T4: 事务A结束。
T5: 事务B: 快照为Snapshot = '201:201'。事务C: 快照仍为Snapshot = '200:200'

从上图可知，事务隔离级别的原理是由事务快照来实现。

开启A事务

psqlgres> psql -U postgres -d testdb
BEGIN;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =854;
UPDATE payment SET amount=1.1 WHERE payment_id=854;
SELECT txid_current_snapshot();

开启B事务

psqlgres> psql -U postgres -d testdb
BEGIN TRANSACTION ISOLATION LEVEL  READ COMMITTED;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =10244;
UPDATE payment SET amount=1.2 WHERE payment_id=10244;
SELECT txid_current_snapshot();

开启C事务

psqlgres> psql -U postgres -d testdb
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT txid_current_snapshot();
SELECT txid_current();
SELECT * FROM payment where payment_id =9643;
UPDATE payment SET amount=1.3 WHERE payment_id=9643;
SELECT txid_current_snapshot();

结束事务A

commit

在B事务中查询快照

SELECT txid_current_snapshot();

在C事务中查询快照

SELECT txid_current_snapshot();

事务隔离的冲突类型

REDCOMMITED

更新同一行造成冲突

事务A

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT txid_current();
SELECT txid_current_snapshot();
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

事务B

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT txid_current();
SELECT txid_current_snapshot();
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

REPEATABLE READ

更新同一行造成冲突

事务A

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT txid_current();
SELECT txid_current_snapshot();
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

事务B

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT txid_current();
SELECT txid_current_snapshot();
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();
卡住

事务A

commit;

事务B

ERROR:  could not serialize access due to concurrent update

更新同一行造成冲突

事务A

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT txid_current();
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

事务B

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

事务A提交

commit;

在事务A开始后，立即开始。
4. 事务B更新：

SELECT * FROM payment WHERE payment_id=8965 ;
SELECT txid_current();
SELECT txid_current_snapshot();
UPDATE payment SET amount=1.6 WHERE payment_id=8965;
ERROR:  could not serialize access due to concurrent update

更新不同行（无冲突）

事务A

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

事务B

testdb=# START TRANSACTION ISOLATION LEVEL READ COMMITTED;
testdb=# UPDATE payment SET amount=1.3 WHERE payment_id=9643; 
this transaction is being blocked

SERIALIZABLE

START TRANSACTION ISOLATION LEVEL SERIALIZABLE

序列化原理

序列化异常的冲突场景

假阳性序列化异常False-Positive Serialization Anomalies

读取和更新不同行造成冲突

事务A

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT txid_current_snapshot();
SELECT * FROM payment where payment_id=9643;
UPDATE payment SET amount=1.5 WHERE payment_id=8965;
SELECT txid_current_snapshot();

事务B

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT txid_current_snapshot();
SELECT* FROM payment where payment_id=8965;
UPDATE payment SET amount=1.3 WHERE payment_id=9643; 
SELECT txid_current_snapshot();

提交

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

事务状态源码解读

代码公式阅读说明

Tuple_1(199) UPDATE payment SET amount=1.3 WHERE payment_id=9643; 
Tuple_2(200) UPDATE payment SET amount=1.2 WHERE payment_id=10244;

ABORTED 状态

/* t_xminstatus = ABORTED */
Rule 1: IF t_xminstatus is 'ABORTED' THEN
RETURN 'Invisible'
END IF

Rule1

--ABORTED 事务
Rule1(Tuple_1):Status(t_xmin:199) = ABORTED  ⇒ Visible
Rule1(Tuple_2):Status(t_xmin:199) = ABORTED  ⇒ Visible
Rule1(Tuple_1):Status(t_xmin:200) = ABORTED  ⇒ Visible
Rule1(Tuple_2):Status(t_xmin:200) = ABORTED  ⇒ Visible

IN_PROGRESS 状态

/* t_xminstatus = IN_PROGRESS*/
IF t_xmin status is 'IN_PROGRESS' THEN
		IF t_xmin= current_txid THEN
Rule 2:      IF t_xmax= INVALID THEN
					RETURN 'Visible'
Rule 3:      ELSE /* this tuple has been deleted or updated by the current transaction itself. */
			        RETURN 'Invisible'
		END IF
Rule 4: ELSE /* t_xmin≠ current_txid*/
				RETURN 'Invisible'
		END IF
END IF

Rule2

Rule2(Tuple_1):
Status(t_xmin:199) = IN_PROGRESS 
∧ t_xmin:199 = current_txid:199 
∧ t_xmax = INVALID
⇒ Visible



Rule2(Tuple_2):
Status(t_xmin:200) = IN_PROGRESS 
∧ t_xmin:200 = current_txid:200 
∧ t_xmax = INVALID 
 ⇒ Visible

Rule3

Rule2(Tuple_1):
Status(t_xmin:199) = IN_PROGRESS 
∧ t_xmin:199 = current_txid:199 
∧ t_xmax = VALID
⇒ InVisible



Rule2(Tuple_2):
Status(t_xmin:200) = IN_PROGRESS 
∧ t_xmin:200 = current_txid:200 
∧ t_xmax = VALID 
 ⇒ InVisible

Rule4

Rule3(Tuple_2):
Status(t_xmin:199) = IN_PROGRESS 
∧ t_xmin:199 ≠ current_txid:200 
 ⇒ InVisible

Rule3(Tuple_1):
Status(t_xmin:200) = IN_PROGRESS 
∧ t_xmin:200 ≠ current_txid:199 
⇒ InVisible

COMMITED 状态

/* t_xmin status = COMMITTED */
		IF t_xmins tatus is 'COMMITTED' THEN
Rule 5:      IF t_xmin is active in the obtained transaction snapshot THEN
				RETURN 'Invisible'
Rule 6: ELSE IF t_xmax= INVALID  OR status of t_xmax is 'ABORTED' THEN
				RETURN 'Visible'
		ELSE IF t_xmax status is 'IN_PROGRESS' THEN
Rule 7:      IF t_xmax = current_txid THEN
				RETURN 'Invisible'
Rule 8:      ELSE /* t_xmax≠ current_txid*/
                 RETURN 'Visible'
             END IF
             ELSE IF t_xmax status is 'COMMITTED' THEN
Rule 9:      IF t_xmax is active in the obtained transaction snapshot THEN
					RETURN 'Visible'
Rule 10:      ELSE
					RETURN 'Invisible'
			 END IF
		END IF
	END IF


Rule10(Tuple_1): 
Status(t_xmin:199) = COMMITTED 
∧Status(t_xmax:200) = COMMITTED 
∧Snapshot(t_xmax:200) ≠ active 
⇒Invisible


Rule10(Tuple_1): 
Status(t_xmin:199) = COMMITTED 
∧Status(t_xmax:200) = COMMITTED 
∧Snapshot(t_xmax:200) = active 
⇒ visible

REPEATEABLE READ 状态

Rule10(Tuple_1): 
Status(t_xmin:199) = COMMITTED 
∧Status(t_xmax:200) = COMMITTED 
∧Snapshot(t_xmax:200) = active 
⇒ Invisible


Rule10(Tuple_1): 
Status(t_xmin:199) = COMMITTED 
∧Status(t_xmax:200) = COMMITTED 
∧Snapshot(t_xmax:200) = active 
⇒ Invisible